package com.apetrenko.avaya.callstore.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.apetrenko.avaya.callstore.client.Config;
import com.apetrenko.avaya.callstore.client.Filter;
import com.apetrenko.avaya.callstore.client.Record;

public class DbAdapter {

	private Connection iConnection;
	private PreparedStatement iWriteStatement;
	private Statement iReadStatement;
	private SimpleDateFormat iDateFormat;
	private PreparedStatement iGetStatement;

	public void init(Config aConfig) {
		iDateFormat = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");

		String vDbDriver = aConfig.getDbDriver();
		String vDbUrl = aConfig.getDbUrl();
		String vDbUser = aConfig.getDbUser();
		String vDbPassword = aConfig.getDbPassword();

		try {
			loadDriver(vDbDriver);
			createConnection(vDbUrl, vDbUser, vDbPassword);
			createStatements();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	private void createStatements() throws Exception {
		iReadStatement = iConnection.createStatement();
		iWriteStatement = iConnection
				.prepareStatement("insert into records (original_name, archive_name, originator, operator, dt_start, duration) values (?, ?, ?, ?, ?, ?)");
		iGetStatement = iConnection.prepareStatement("select * from records where mainkey = ?");

	}

	private void createConnection(String aDbUrl, String aDbUser,
			String aDbPassword) throws Exception {
		iConnection = DriverManager.getConnection(aDbUrl, aDbUser, aDbPassword);
	}

	private void loadDriver(String aDbDriver) throws Exception {
		Class.forName(aDbDriver).newInstance();
	}

	public void close() {
		try {
			if (iWriteStatement != null) {
				iWriteStatement.close();
				iWriteStatement = null;
			}
			if (iConnection != null) {
				iConnection.close();
				iConnection = null;
			}
		} catch (Exception e) {
		}

	}

	public void storeRecordInfo(RecordInfo aInfo) {
		try {
			iWriteStatement.clearParameters();
			iWriteStatement.setString(1, aInfo.getOriginalFileName());
			iWriteStatement.setString(2, aInfo.getArchiveFileName());
			iWriteStatement.setString(3, aInfo.getOriginator());
			iWriteStatement.setString(4, aInfo.getOperator());
			iWriteStatement.setTimestamp(5, new Timestamp(aInfo.getStartTime()
					.getTime()));
			iWriteStatement.setInt(6, aInfo.getDuration());
			iWriteStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public Record[] getRecords(Filter aFilter) {
		ArrayList<Record> vList = new ArrayList<Record>();
		Record vRecord = null;
		try {
			ResultSet vRs = iReadStatement
					.executeQuery("select * from records");
			while (vRs.next()) {
				vRecord = new Record();
				vRecord.setId(vRs.getLong("mainkey"));
				vRecord.setOriginalName(vRs.getString("original_name"));
				vRecord.setArchivedName(vRs.getString("archive_name"));
				vRecord.setOriginator(vRs.getString("originator"));
				vRecord.setOperator(vRs.getString("operator"));
				vRecord.setDate(getStartTime(vRs
						.getTimestamp("dt_start")));
				vRecord.setDuration(getDuration(vRs.getInt("duration")));
				vList.add(vRecord);
			}
		} catch (SQLException e) {

		}
		Record[] vArr = {};
		return vList.toArray(vArr);

	}

	private String getStartTime(Timestamp aTimestamp) {
		return iDateFormat.format(aTimestamp);
	}

	private String getDuration(int aInt) {
		int vHours = (int) Math.floor(aInt / 3600.0);
		String vStrHours = (vHours > 9) ? "" + vHours : "0" + vHours;
		int vMins = (int) Math.floor((aInt - (3600 * vHours)) / 60.0);
		String vStrMins = (vMins > 9) ? "" + vMins : "0" + vMins;
		int vSecs = aInt - (3600 * vHours) - (60 * vMins);
		String vStrSecs = (vHours > 9) ? "" + vSecs : "0" + vSecs;
		return vStrHours + ":" + vStrMins + ":" + vStrSecs;
	}

	public Record getRecord(long aRecordId) {
		Record vRecord = null;
		try {
			iGetStatement.clearParameters();
			iGetStatement.setLong(1, aRecordId);
			ResultSet vRs = iGetStatement
					.executeQuery();
			while (vRs.next()) {
				vRecord = new Record();
				vRecord.setId(vRs.getLong("mainkey"));
				vRecord.setOriginalName(vRs.getString("original_name"));
				vRecord.setArchivedName(vRs.getString("archive_name"));
				vRecord.setOriginator(vRs.getString("originator"));
				vRecord.setOperator(vRs.getString("operator"));
				vRecord.setDate(getStartTime(vRs
						.getTimestamp("dt_start")));
				vRecord.setDuration(getDuration(vRs.getInt("duration")));
			}
		} catch (SQLException e) {

		}
		return vRecord;
		
	}

}
